﻿using System;
using System.Collections.Generic;
using System.IO;
using BabyPoetryConference.Base;
using BabyPoetryConference.Entities;
using BabyPoetryConference.Extensions;
using ExcelDataReader;
using Microsoft.Extensions.Configuration;

namespace BabyPoetryConference.DbHelpers
{
    public class PoetryDbHelperExcel : IPoetryDbHelper
    {
        private IConfiguration _Config;
        private string _PoetryFileName;
        private string _QuestionFileName;
        public PoetryDbHelperExcel(Config config)
        {
            _Config = config.Configuration;
            _PoetryFileName = Path.Combine(_Config.GetSection("path").GetString("poetry_file_path"), _Config.GetSection("poetry_file").GetString("poetry_file_name"));
            _QuestionFileName = Path.Combine(_Config.GetSection("path").GetString("question_file_path"), _Config.GetSection("question_file").GetString("question_file_name"));
        }

        public Dictionary<string, List<string>> GetAuthorDynasty()
        { 
            string sheetName = _Config.GetSection("poetry_file").GetString("sheet_author_dynasty_name");
            return BuildDictionaryFromSheet(_PoetryFileName, sheetName);
        }

        /// <summary>
        /// 获取飞花令字库
        /// </summary>
        /// <returns></returns>
        public List<string> GetFlowerOrder()
        {
            var ret = new List<string>();
            var sheetName = _Config.GetSection("poetry_file").GetString("sheet_flower_order_name");

            using (var stream = File.Open(_PoetryFileName, FileMode.Open, FileAccess.Read))
            {
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    var ds = reader.AsDataSet();
                    var table = ds.Tables[sheetName];
                    var rowCount = table.Rows.Count;
                    for(int i = 0; i < rowCount; i++)
                    {
                        var val = table.Rows[i][0].ToString();
                        if (!string.IsNullOrEmpty(val))
                        {
                            ret.Add(val);
                        }
                    }
                }
            }
            return ret;
        }
        
        /// <summary>
        /// 获取高级飞花令字库
        /// </summary>
        /// <returns></returns>
        public Dictionary<string, List<string>> GetNewFlowerOrder()
        {
            string sheetName = _Config.GetSection("poetry_file").GetString("sheet_new_flower_order_name");
            return BuildDictionaryFromSheet(_PoetryFileName, sheetName);
        }

        /// <summary>
        /// 获取成语列表
        /// </summary>
        /// <returns></returns>
        public List<string> GetIdioms()
        {
            List<string> ret = new List<string>();
            string sheetName = _Config.GetSection("poetry_file").GetString("sheet_idiom_name");
            Dictionary<string, List<string>> idiomsAll = BuildDictionaryFromSheet(_PoetryFileName, sheetName);
            List<string> idioms = idiomsAll["成语"];
            if(idioms != null)
            {
                foreach(var idiom in idioms)
                {
                    // 只使用4字成语看起来比较整齐
                    if (idiom.Length == 4)
                    {
                        ret.Add(idiom);
                    }
                }
            }
            return ret;
        }

        public void GetQuestionStore(Dictionary<string, Question> questionDict, List<string> questionNoList)
        {
            using (var stream = File.Open(_QuestionFileName, FileMode.Open, FileAccess.Read))
            {
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    var ds = reader.AsDataSet();
                    //先加载目录
                    var sheetName = _Config.GetSection("question_file").GetString("sheet_table_of_contents_name");
                    var table = ds.Tables[sheetName];
                    for(int i=1;i< table.Rows.Count;i++)
                    {
                        var rowOfContent = table.Rows[i];
                        var loadOfContent = rowOfContent[0].ToString();
                        var noOfContent = rowOfContent[1].ToString();
                        var subjectOfContent = rowOfContent[2].ToString();
                        if(loadOfContent.Equals("Y", StringComparison.OrdinalIgnoreCase))
                        {
                            var tableOfSubject = ds.Tables[subjectOfContent];
                            for(int j=1;j< tableOfSubject.Rows.Count; j++)
                            {
                                var row = tableOfSubject.Rows[j];
                                var load = row[0].ToString();
                                var no = row[1].ToString();
                                var category = row[2].ToString();
                                var questionContent = row[3].ToString();
                                var answer = row[4].ToString();
                                var explain = row[5].ToString();

                                if(load.Equals("Y", StringComparison.OrdinalIgnoreCase))
                                {
                                    var question = new Question();
                                    var questionNo = $"{subjectOfContent}_{no}";
                                    question.No = questionNo;
                                    question.Subject = "question";
                                    question.Content = questionContent;
                                    question.Answer = answer;
                                    question.Explain = explain;
                                    questionDict.Add(questionNo, question);
                                    questionNoList.Add(questionNo);
                                }
                            }
                        }
                    }

                }
            }
        }

        public void GetPoetryStore(Dictionary<string, Poem> poetry_store, List<string> poetry_name_box, Dictionary<string, List<string>> authorDynastyDict)
        {
            var sheetName = _Config.GetSection("poetry_file").GetString("sheet_poetrys_name"); 
            using (var stream = File.Open(_PoetryFileName, FileMode.Open, FileAccess.Read))
            {
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    var ds = reader.AsDataSet();
                    var table = ds.Tables[sheetName];
                    int rowCount = table.Rows.Count;
                    for (int i=1;i<rowCount;i++)
                    {
                        var row = table.Rows[i];
                        var load = row[0].ToString();
                        var title = row[1].ToString();
                        var subTitle = row[2].ToString();
                        var author = row[3].ToString();
                        var content = row[4].ToString();
                        var sentences = new List<string>(content.Split('\n'));
                        var item1 = "";
                        if(!subTitle.Equals("no", StringComparison.OrdinalIgnoreCase))
                        {
                            item1 = $".{subTitle}";
                        }
                        var dynasty = GetDynastyByAuthor(author, authorDynastyDict);
                        var poetry_name = $"【 {title}{ item1} 】{author}({dynasty})";
                        var poetry = new Poem(poetry_name, title, subTitle, author, dynasty, sentences);
                        poetry_store.Add(poetry_name, poetry);
                        poetry_name_box.Add(poetry_name);
                    }
                }
            }
        }

        private string GetDynastyByAuthor(string author, Dictionary<string, List<string>> authorDynastyDict)
        {
            foreach(var key in authorDynastyDict.Keys)
            {
                if (authorDynastyDict[key].Contains(author))
                {
                    return key;
                }
            }
            return "未知";
        }

        private Dictionary<string, List<string>> BuildDictionaryFromSheet(string fileName, string sheetName, bool removeEmptyString = true)
        {
            Dictionary<string, List<string>> ret = new Dictionary<string, List<string>>();
            using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
            {
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    var ds = reader.AsDataSet();
                    // The result of each spreadsheet is in result.Tables
                    var table = ds.Tables[sheetName];
                    var rowCount = table.Rows.Count;
                    var colCount = table.Columns.Count;
                    var titleIndex = new Dictionary<int, string>();
                    if (rowCount > 0)
                    {
                        // 生成title
                        for (int i = 0; i < colCount; i++)
                        {
                            var title = table.Rows[0][i].ToString();
                            ret.Add(title, new List<string>());
                            titleIndex.Add(i, title);
                        }
                        // 遍历添加内容
                        for (int i = 1; i < rowCount; i++)
                        {
                            for (int j = 0; j < colCount; j++)
                            {
                                var item = table.Rows[i][j].ToString();
                                if(removeEmptyString && string.IsNullOrEmpty(item))
                                {
                                    //不添加
                                }
                                else
                                {
                                    var title = titleIndex[j];
                                    ret[title].Add(item);
                                }
                            }
                        }
                    }
                }
            }

            return ret;
        }
    }
}
